<?php
	require_once("config.php");
	require_once("db_access.php");

	//TODO: test functions in test.php
	
	// Auxillary Functions************************************************************
	
	//	Generates a feed url for a userId and feed name
	function GenerateFeedUrl( $_userId, $_feedName )
	{
		global $hostname, $username, $password, $database;
		
		$userId = sanitizeInput($_userId);
		$feedName = sanitizeInput($_feedName);
		$url = $userId."-".$feedName;
		try
		{
			$query = 'SELECT * FROM socialfilter.feed WHERE url="'.$url.'";';
			do
			{
				// Append a random character to url and rehash it
				$url.= rand();
				$url = md5($url).".xml";
				$result = queryDatabase($hostname, $username, $password, $database, $query);
			} while ( 0 !== count($result) );
		}
		catch ( Exception $e )
		{
			return $e->getMessage();
		}
		
		return $url;
	}
	
	function GetFeedUrl( $_feedId )
	{
		global $hostname, $username, $password, $database;
		
		$feedId = sanitizeInput($_feedId);
		
		try
		{
			$query = "SELECT url FROM socialfilter.feed WHERE feedid=5;";
			$result = queryDatabase($hostname, $username, $password, $database, $query);
			if ( count($result) != 1 )
			{
				return null;
			}
			return $result[0][0];
		}
		catch ( Exception $e )
		{
			return null;
		}
	}
	
	//	Get the last id inserted into any table
	function GetLastInsertedId()
	{
		global $hostname, $username, $password, $database;
		$query = "SELECT LAST_INSERT_ID();";
		$result = queryDatabase($hostname, $username, $password, $database, $query);
		return $result[0][0];
	}
	
	//	Lock the specified tables
	//	@param		_tables		Array of strings listing table names and aliases, e.g. "users as u"
	function LockTables( $_tables )
	{
		global $hostname, $username, $password, $database;
		foreach( $_tables as $table )
		{
			$tables[] = sanitizeInput($table);
		}
		$tableString = implode( " WRITE, ", $tables );
		$query = "LOCK TABLES " . $tableString . " WRITE;";
		queryDatabase($hostname, $username, $password, $database, $query);
	}
	
	// Unlock all tables
	function UnlockTables()
	{
		global $hostname, $username, $password, $database;
		$query = "UNLOCK TABLES;";
		queryDatabase($hostname, $username, $password, $database, $query);
	}
	
	// User management****************************************************************
	
	// Retrieves the user id of a given account, or -1 if the user could't be found
	function GetUserId( $_accountName, $_siteName, $_token )
	{
		global $hostname, $username, $password, $database, $tokenKey;
		
		$accountName = sanitizeInput($_accountName);
		$siteName = sanitizeInput($_siteName);
		$token = sanitizeInput($_token);
		
		try
		{
			$query = 'SELECT * FROM user WHERE AES_ENCRYPT("'.$token.'", "'.$tokenKey.'")=token AND account_name="'.$accountName.'" AND EXISTS (SELECT * FROM socialfilter.site WHERE site_siteid=siteid AND name="'.$siteName.'");';
			$result = queryDatabase($hostname, $username, $password, $database, $query);
			if ( count($result) )
			{
				return $result[0][0];
			}
			else
			{
				return null;
			}
		}
		catch (Exception $e)
		{
				return null;
		}
	}
	
	//	Given a token, retrieve all relevant user info for all accounts for that user
	//	@param		_userId		The id of the user to get accounts for
	//	@return		An array of arrays, the parent array is a list of accounts, or null if the function fails.
	//						Each child array is info associated with that account. Each child has the following keys:
	//							*account_name:	name of the account
	//							*name: 					name of the site the account is for
	function GetUserAccounts( $_userId )
	{
		global $hostname, $username, $password, $database;
		$userId = sanitizeInput($_userId);
		try
		{
			$query = 'SELECT account_name, name FROM socialfilter.user, socialfilter.site WHERE userid='.$userId.' AND siteid=site_siteid';
			$results = queryDatabase($hostname, $username, $password, $database, $query);
			$accounts = array();
			foreach( $results as $row )
			{
				$fields = array();
				$fields['account_name'] = $row[0];
				$fields['name'] = $row[1];
				$accounts[] = $fields;
			}
			return $accounts;
		}
		catch ( Exception $e )
		{
			return null;
		}
	}
	
	//	Add a new user to the database
	//	@param		_accountName	Account name for the site
	//	@param		_siteName			Name of the site the account belongs to
	//	@param		_token				The token to be used for accessing this account
	//	@param		_userId				input: should be null,
	//													output: userId of the new user or of an existing user if the account already existed
	//	@return		null if the function failed, 0 if the account already existed, or 1 if a user was created
	function AddNewUser( $_accountName, $_siteName, $_token, &$_userId )
	{
		$result = AddAccount( $_userId, $_accountName, $_siteName, $_token );
		echo($result);
		if ( $result !== 0 && $result !== null )
		{
			return $result;
		}
		else
		{
			return null;
		}
	}
	
	// Merge two users into one
	//	@param		_userIdA	Dominant userId to merge, will be retained
	//	@param		_userIdB	Non-dominant userId to merge, will be replaced
	//	@return		True if the merge was successful, or false otherwise
	function MergeUsers( $_userIdA, $_userIdB )
	{
		global $hostname, $username, $password, $database;
		
		$userIdA = sanitizeInput($_userIdA);
		$userIdB = sanitizeInput($_userIdB);
		try
		{
			$tables[] = "socialfilter.user";
			LockTables( $tables );
			//get the site ids for the 2 users
			$query = 'SELECT userid, site_siteid FROM socialfilter.user WHERE userid='.$userIdA.' OR userid='.$userIdB.' ORDER BY userid;';
			$results = queryDatabase($hostname, $username, $password, $database, $query);
			//make sure there's no overlap in sites
			$aSites = array();
			$bSites = array();
			foreach( $results as $result )
			{
				if ( $result[0] == $userIdA )
				{
					$aSites[] = $result[1];
				}
				else if ( $result[0] == $userIdB )
				{
					$bSites[] = $result[1];
				}
			}
			$intersection = array_intersect($aSites, $bSites);
			if ( count($intersection) > 0 )
			{
				UnlockTables();
				return false;
			}
			// Make sure both users exist
			if ( count($aSites) === 0 || count($bSites) === 0 )
			{
				UnlockTables();
				return false;
			}
			//replace all the instances of idB with idA in user AND feed
			$query = 'UPDATE socialfilter.user SET userid='.$userIdA.' WHERE userid='.$userIdB.';';
			queryDatabase($hostname, $username, $password, $database, $query);
			return true;
		}
		catch (Exception $e)
		{
			UnlockTables();
			return false;
		}
	}
	
	//	Add an account to a user, or if no user exists, create a new user
	//	@param		_userId				output: User Id to add the account to, or null to create a new account.
	//													If the account already exists, the userId of the owner will be written to userId
	//													If _userId was null or did not exist in the database, the userId of the new user
	//													created will be written to _userId
	//													If an error occurred, _userId will be set to null
	//	@param		_accountName	Account name for the site
	//	@param		_siteName			Name of the site the account belongs to
	//	@param		_token				The token to be used for accessing this account
	//	@return		null if the function failed, 0 if the user already existed, or 1 if a new user was created
	function AddAccount( &$_userId, $_accountName, $_siteName, $_token )
	{
		global $hostname, $username, $password, $database, $tokenKey;
		
		$userId = sanitizeInput($_userId);
		$accountName = sanitizeInput($_accountName);
		$siteName = sanitizeInput($_siteName);
		$token = sanitizeInput($_token);
		
		if ( $userIdLookup = GetUserId($accountName, $siteName, $token) !== null ) //verify that the account doesn't already exist in the database //verify that the account doesn't already exist in the database
		{
			// Try to merge the two accounts, userId is dominant
			if ( MergeUsers($userId, $userIdLookup) === true )
			{
				return 0;
			}
			// Failed to merge the accounts
			else
			{
				$_userId = $userIdLookup;
				return null;
			}
		}
		
		try
		{
			// Add account to existing user, and create a new user if necessary (depends on whether userId is null or not)
			if ( $userId == null )
			{
				$userId = "NULL";
			}
			$query = 'INSERT INTO socialfilter.user (userid, account_name,site_siteid,token) VALUES ('.$userId.', "'.$accountName.'", (select s.siteid from socialfilter.site as s where s.name="'.$siteName.'"), AES_ENCRYPT("'.$token.'", "'.$tokenKey.'"));';
			queryDatabase($hostname, $username, $password, $database, $query);
			if ( $userId === null ) // We created a new user for the account
			{
				$_userId = GetLastInsertedId();
				return 1;
			}
			else // We added the account to an existing user
			{
				return 0;
			}
		}
		catch (Exception $e)
		{
			$_userId = null;
			return null;
		}
	}
	
	//	Store a given token for a given user and account in the database
	//	@param		_accountName	Account name for the site
	//	@param		_siteName			Name of the site the account belongs to
	//	@param		_token				The new token to be used for accessing this account
	//	@return		True if the token was set successfully, or false otherwise
	function SetToken( $_accountName, $_siteName, $_token )
	{
		global $hostname, $username, $password, $database, $tokenKey;
		
		$accountName = sanitizeInput($_accountName);
		$siteName = sanitizeInput($_siteName);
		$token = sanitizeInput($_token);
		
		$query = 'UPDATE socialfilter.user SET token=AES_ENCRYPT("'.$token.'", "'.$tokenKey.'" ) WHERE account_name="'.$accountName.'" AND EXISTS (SELECT site_siteid=siteid FROM socialfilter.site WHERE name="'.$siteName.'");';
		try
		{
			$result = queryDatabase($hostname, $username, $password, $database, $query);
			if ($result > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		catch (Exception $e)
		{
			return false;
		}
	}
	
	//	Retrieve a token for a given user and account from the database
	//	@param		_accountName	Account name for the site
	//	@param		_siteName			Name of the site the account belongs to
	//	@return		The requested token, or null if it couldn't be found
	function GetToken( $_accountName, $_siteName )
	{
		global $hostname, $username, $password, $database, $tokenKey;
		
		$accountName = sanitizeInput($_accountName);
		$siteName = sanitizeInput($_siteName);
		
		$query = 'SELECT AES_DECRYPT(token, "'.$tokenKey.'") FROM socialfilter.user WHERE account_name="'.$accountName.'" AND EXISTS (SELECT site_siteid=siteid FROM socialfilter.site WHERE name="'.$siteName.'");';
		try
		{
			$result = queryDatabase($hostname, $username, $password, $database, $query);
			if (count($result) > 0)
			{
				return $result[0][0];
			}
			else
			{
				return null;
			}
		}
		catch (Exception $e)
		{
			return null;
		}
	}
	
	// Feed management****************************************************************
	
	//	Retrieves an array of feeds for a given user
	//	@param		_userId		The id of the user to get feeds for
	//	@return		An array of arrays, the parent array is a list of feeds, or null if the function fails.
	//						Each child array is info associated with that feed. Each child has the following keys:
	//							*feedid:				id of the feed used in the database
	//							*name: 					name of the feed, given by the user
	//							*url:						url of the feed for RSS, does NOT include the site's address, i.e. test.xml NOT http:\\localhost\test.xml
	//							*last_updated:	timestamp, the last time this feed was updated
	function GetFeeds( $_userId )
	{
		global $hostname, $username, $password, $database;
		
		$userId = sanitizeInput($_userId);
		
		try
		{
			$query = 'SELECT feedid, name, url, last_updated FROM socialfilter.feed WHERE user_userid='.$userId.' AND deleted=FALSE;';
			$results = queryDatabase($hostname, $username, $password, $database, $query);
			$feeds = array();
			foreach( $results as $row )
			{
				$fields = array();
				$fields['feedid'] = $row[0];
				$fields['name'] = $row[1];
				$fields['url'] = $row[2];
				$fields['last_updated'] = $row[3];
				$feeds[] = $fields;
			}
			return $feeds;
		}
		catch ( Exception $e )
		{
			return null;
		}
	}
	
	//	Creates a feed for a given user
	//	@param		_userId		The id of the owner of the feed
	//	@param		_name			The name of the feed, displayed to the user
	//	@return		The feedId of the newly created feed, or null if the function fails
	function CreateFeed( $_userId, $_name )
	{
		global $hostname, $username, $password, $database;
		
		$userId = sanitizeInput($_userId);
		$name = sanitizeInput($_name);
		try
		{
			// See if a feed already exists with that name for that user
			$query = 'SELECT * FROM socialfilter.feed WHERE user_userid='.$userId.' AND name="'.$name.'";';
			if ( count(queryDatabase($hostname, $username, $password, $database, $query)) !== 0 )
			{
				echo "error";
				return null;
			}
			$url = GenerateFeedUrl( $userId, $name );
			// Create feed
			$query = 'INSERT INTO socialfilter.feed (name, url, user_userid, last_updated) VALUES ("'.$name.'", "'.$url.'", '.$userId.', SUBTIME(NOW(), "24:00:00"));';
			queryDatabase($hostname, $username, $password, $database, $query);
			return GetLastInsertedId();
		}
		catch ( Exception $e )
		{
			echo $e->getMessage();
			return null;
		}
	}
	
	//	Deletes a feed from a given user
	//	@param		_userId		The id of the owner of the feed, used for varification purposes
	//	@param		_feedId		The id of the feed to delete
	//	@return		True if the operation was successful or false otherwise
	function DeleteFeed( $_userId, $_feedId )
	{
		global $hostname, $username, $password, $database;
		$userId = sanitizeInput($_userId);
		$feedId = sanitizeInput($_feedId);
		try
		{			
			// mark feed as deleted
			$query = 'UPDATE socialfilter.feed SET deleted=TRUE WHERE feedid='.$feedId.' AND user_userid='.$userId.';';
			if ( queryDatabase($hostname, $username, $password, $database, $query) !== 1 )
			{
				return false;
			}
			else
			{
				return true;
			}
		}
		catch ( Exception $e )
		{
			return false;
		}
	}
	
	//	Request for a given feed to be priority updated
	//@param	_userId		id of the user who owns the feed, for verification purposes
	//@param	_feedId		id of the feed to update
	//@return	True if the feed was added to the update requests table, or false if the function failed (feed already existed in the table, userId didn't own feed, or other db errors)
	function RequestFeedUpdate( $_userId, $_feedId )
	{
		global $hostname, $username, $password, $database;
		$feedId = sanitizeInput($_feedId);
		try
		{
			$query = 'INSERT INTO socialfilter.feed_update_requests (feed_feedid) VALUES((SELECT feedid FROM socialfilter.feed WHERE feedid='.$feedId.' AND user_userid='.$userId.' AND deleted=FALSE));';
			queryDatabase($hostname, $username, $password, $database, $query);
			return true;
		}
		catch ( Exception $e )
		{
			return false;
		}
	}
	
	// Filter management**************************************************************
	
	//	Retrieves an array of filters for a given feed for a given user
	//	@param		_userId		The id of the user to get feeds for
	//	@param		_feedId		The id of the feed to retrieve filters for
	//	@return		An array of arrays, the parent array is a list of filters, or null if the function fails.
	//						Each child array is info associated with that filter. Each child has the following keys:
	//							*filterid:				id of the filter used in the database
	//							*filter_string: 	string of filters applied to this feed
	function GetFilters( $_userId, $_feedId)
	{
		global $hostname, $username, $password, $database;
		
		$userId = sanitizeInput($_userId);
		$feedId = sanitizeInput($_feedId);
		try
		{
			$query = 'SELECT filterid, filter_string FROM socialfilter.filter AS t, socialfilter.feed AS f WHERE feed_feedid=feedid AND feed_feedid='.$feedId.'. AND deleted=FALSE AND user_userid='.$userId.';';
			$results = queryDatabase($hostname, $username, $password, $database, $query);
			$filters = array();
			foreach( $results as $row )
			{
				$fields = array();
				$fields['filterid'] = $row[0];
				$fields['filter_string'] = $row[1];
				$filters[] = $fields;
			}
			return $filters;
		}
		catch ( Exception $e )
		{
			return null;
		}
	}
	
	//	Creates a filter to a given feed for a given user
	//	@param		_userId				The id of the owner of the filter
	//	@param		_feedId				The id of the feed the filter will be applied to
	//	@param		_filterString	The id of the feed the filter will be applied to
	//	@return		The filterId of the newly created filter, or null if the function fails
	function CreateFilter( $_userId, $_feedId, $_filterString )
	{
		global $hostname, $username, $password, $database;
		$userId = sanitizeInput($_userId);
		$feedId = sanitizeInput($_feedId);
		$filterString = sanitizeInput($_filterString);
		try
		{
			$query = 'INSERT INTO socialfilter.filter (filter_string, feed_feedid) VALUES ("'.$filterString.'", (SELECT feedid FROM socialfilter.feed WHERE feedid='.$feedId.' AND deleted=false AND user_userid='.$userId.'));';
			queryDatabase($hostname, $username, $password, $database, $query);
			return GetLastInsertedId();
		}
		catch ( Exception $e )
		{
			echo $e->getMessage();
			return null;
		}
	}
	
	//	Deletes a filter from a given feed for a given user
	//	@param		_userId		The id of the owner of the feed, used for varification purposes
	//	@param		_filterId	The id of the filter to delete
	//	@return		True if the operation was successful or false otherwise
	function DeleteFilter( $_userId, $_filterId )
	{
		global $hostname, $username, $password, $database;
		$userId = sanitizeInput($_userId);
		$filterId = sanitizeInput($_filterId);
		try
		{
			// Delete filter, confirm that the user does in fact own the feed they are trying to delete
			$query = 'DELETE FROM socialfilter.filter WHERE filterid='.$filterId.' AND EXISTS (SELECT * FROM socialfilter.feed WHERE feed_feedid=feedid AND user_userid='.$userId.');';
			if ( count(queryDatabase($hostname, $username, $password, $database, $query)) !== 1 )
			{
				return false;
			}
			else
			{
				return true;
			}
		}
		catch ( Exception $e )
		{
			//echo $e->getMessage();
			return false;
		}
	}
?>